package ruteodosciudades.algoritmo.extra;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author gonzaleza
 */
public class ManageDB {
    //Atributos de la clase

    private ConnectDB conexion;
    private Connection conn;
    private ResultSet rs;
    private Statement s;
    private String database;

    public String getDatabase() {
        return database;
    }

    public void setDatabase(String database) {
        this.database = database;
    }

    public ManageDB(String database){
        this.database = database;
    }
    /**
     * Realiza la consulta a la base de datos
     * Retorna lista de nodos
     **/
    public List<Registro> getListaNodos() throws SQLException {

        List<Registro> result = new ArrayList();
        String sql = " SELECT * FROM ciudades";


        try {
            conexion = new ConnectDB(database);
            conn = conexion.getConnection();
            s = conn.createStatement();
            s.executeQuery(sql);
            rs = s.getResultSet();
            while (rs != null && rs.next()) {
                result.add(new Registro(rs.getString("Padre"), rs.getString("Nodo"), rs.getInt("Costo")));
            }
            conn.close();
            s.close();
            rs.close();
        } catch (SQLException e) {
            System.out.println("SQL ERROR");
            e.printStackTrace();
        }
        System.out.println("nodos : " + result.size());
        return result;
    }

    /**
     * Busca un nodo en la base de datos
     *
     * */
    public NodoImg findNodo(String idVertice) {
        NodoImg result = null;

        String sql = " SELECT * FROM nodo WHERE idnodo LIKE '%" + idVertice + "%'";


        try {
            conexion = new ConnectDB(database);
            conn = conexion.getConnection();
            s = conn.createStatement();
            s.executeQuery(sql);
            rs = s.getResultSet();

            if (rs != null && rs.next()) {
                result = new NodoImg(new Nodo(rs.getString("idnodo"), rs.getString("nombre")), rs.getInt("x"), rs.getInt("y"));
            }

            conn.close();
            s.close();
            rs.close();
        } catch (SQLException e) {
            System.out.println("SQL ERROR");
            e.printStackTrace();
        }
        return result;
    }

    public NodoImg updateNodoCoordenado(Nodo node) {
        NodoImg nodeCoordenado = null;
        String sql = null;
        if (node.getIdnodo() == null) {
            sql = " SELECT nombre, x, y FROM nodo WHERE idnodo LIKE '" + node.getName().trim() + "'";
            node.setIdnodo(node.getName());
        } else {
            sql = " SELECT nombre, x, y FROM nodo WHERE idnodo LIKE '" + node.getIdnodo().trim() + "'";
        }

        try {
            conexion = new ConnectDB(database);
            conn = conexion.getConnection();
            s = conn.createStatement();
            s.executeQuery(sql);
            rs = s.getResultSet();

            if (rs != null && rs.next()) {
                
                node.setName(rs.getString("nombre"));
                nodeCoordenado = new NodoImg(node, rs.getInt("x"), rs.getInt("y"));
            }

        } catch (SQLException e) {
            System.out.println("SQL ERROR");
            e.printStackTrace();
        } finally {
            try {
                conn.close();
                s.close();
                rs.close();
            } catch (Exception e) {
                System.out.println("SQL ERROR");
                e.printStackTrace();
            }

            return nodeCoordenado;
        }

    }

    public List<String> getListaIdNodos() {
        List<String> result = new ArrayList();

        String sql = " SELECT idnodo FROM nodo ";


        try {
            conexion = new ConnectDB(database);
            conn = conexion.getConnection();
            s = conn.createStatement();
            s.executeQuery(sql);
            rs = s.getResultSet();
            while (rs != null && rs.next()) {
                result.add(rs.getString("idnodo"));
            }
            conn.close();
            s.close();
            rs.close();
        } catch (SQLException e) {
            System.out.println("SQL ERROR");
            e.printStackTrace();
        } finally {
            try {
                conn.close();
                s.close();
                rs.close();
            } catch (Exception e) {
                System.out.println("SQL ERROR");
                e.printStackTrace();
            }

            return result;
        }
    }

    public List<NodoImg> getListaNodosConCoordenadas() {
        List<NodoImg> result = new ArrayList();

        String sql = " SELECT * FROM nodo";


        try {
            conexion = new ConnectDB(database);
            conn = conexion.getConnection();
            s = conn.createStatement();
            s.executeQuery(sql);
            rs = s.getResultSet();
            while (rs != null && rs.next()) {
                result.add(new NodoImg(new Nodo(rs.getString("idnodo"), rs.getString("nombre")), rs.getInt("x"), rs.getInt("y")));
            }
            conn.close();
            s.close();
            rs.close();
        } catch (SQLException e) {
            System.out.println("SQL ERROR");
            e.printStackTrace();
        }
        return result;
    }
}
